package org.cncitrus.data.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Params;
import org.cncitrus.data.manager.ParamsManager;

public class WorldTradeConn {
	private Statement stmt;
	private String TABLE_NAME = "orang_w_jinchumaoyi";
	public WorldTradeConn() throws SQLException{
		stmt = OrangeConn.getInstance().getStatement();
	}
	public String load(String[] ec,String[] ic,String[] year,String[] code) throws Exception{
		String sql = "SELECT * FROM "+TABLE_NAME+" WHERE "+Params.WorldTrade.ec+" IN "+ParamsManager.Array2String(ec)+" AND "+Params.WorldTrade.ic+" IN "+ParamsManager.Array2String(ic)+" AND "+Params.WorldTrade.code+" IN "+ParamsManager.Array2String(code)+" AND "+Params.WorldTrade.year+" IN "+ParamsManager.Array2String(year);
		StringBuffer sb = new StringBuffer();
		ResultSet rs = stmt.executeQuery(sql);
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		rs.last();
		sb.append("<resp count=\""+rs.getRow()+"\"> ");
		rs.beforeFirst();
		while(rs.next()){
			sb.append("<item year=\""+rs.getString(Params.WorldTrade.year).trim()+"\" code=\""+rs.getString(Params.WorldTrade.code).trim()+"\" ec =\""+rs.getString(Params.WorldTrade.ec).trim()+"\" ic =\""+rs.getString(Params.WorldTrade.ic).trim()+"\" total=\""+rs.getString(Params.WorldTrade.total).trim()+"\" money=\""+rs.getString(Params.WorldTrade.money).trim()+"\"/>");
		}
		sb.append("</resp>");
		return sb.toString();
	}
	
	public String insert(String[] year,String[] code,String[] ec,String[] ic,String[] total,String[] money) throws Exception{
		
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO "+TABLE_NAME+" ("+Params.WorldTrade.year+","+Params.WorldTrade.code+","+Params.WorldTrade.ec+","+Params.WorldTrade.ic+","+Params.WorldTrade.total+","+Params.WorldTrade.money+") VALUES ");		
		
		String content = ParamsManager.getInsertString(year,code,ec,ic,total,money);
		sb.append(content);
		stmt.execute(sb.toString());
		sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<resp rc=\"0\"/>");		
		return sb.toString();
	}
}
